package com.example.todolist.database.DAO

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Transaction
import androidx.room.Update
import com.example.todolist.database.model.TodoEntity
import com.example.todolist.entity.TodoCategory
import com.example.todolist.entity.TodoOrder

@Dao
interface TodoDao {
    @Insert
    fun insert(todoEntity: TodoEntity): Long

    @Update
    fun update(todoEntity: TodoEntity)

    @Delete
    fun delete(vararg todoEntity: TodoEntity)

    @Query(
        """
        select todo.title,
        todo.id as todoId,
        todo.end_time as endTime,
        todo.username,
        c.label as categoryName,
        c.color,
        todo.importance,
        todo.is_finish,
        todo.is_expired,
        todo.order_num
        from todo left join category c on todo.category_id=c.category_id
        order by todo.order_num desc
    """
    )
    fun getAllTodoHeader(): List<TodoCategory>

    @Query("select coalesce(max(order_num),0) from todo ")
    fun getMaxOrder(): Int

    @Query("select * from todo where todo.id=:id")
    fun getTodoById(id: Int): TodoEntity

    @Query(
        """
        select todo.title,
        todo.id as todoId,
        todo.end_time as endTime,
        todo.username,
        c.label as categoryName,
        c.color,
        todo.importance,
        todo.is_finish,
        todo.is_expired,
        todo.order_num
        from todo left join category c on todo.category_id=c.category_id
        where todo.category_id=:cId
        order by todo.order_num desc
    """
    )
    fun getTodosByCateId(cId: Int): List<TodoCategory>

    @Query("update todo set is_finish=:isFinish where id=:todoId")
    fun updateTodoFinish(todoId: Int, isFinish: Boolean)

    @Query("select count(*) from todo")
    fun getAllToDoCnt(): Int

    @Query("select count(*) from todo where is_finish=1")
    fun getFinishToDoCnt(): Int

    @Query("update todo set order_num=:to where order_num=:from")
    fun changeOrder(from: Int, to: Int)

    @Query("update todo set order_num=order_num-1 where order_num>:from and order_num<=:to")
    fun upDragOrder(from: Int, to: Int)

    @Query("update todo set order_num=order_num+1 where order_num>:to and order_num<=:from")
    fun downDragOrder(from: Int, to: Int)

    @Transaction
    fun upDrag(from: Int, to: Int) {
        upDragOrder(from, to)
        changeOrder(from, to)
    }

    @Transaction
    fun downDrag(from: Int, to: Int) {
        downDragOrder(from, to)
        changeOrder(from, to)
    }

    @Query("UPDATE todo SET order_num = :orderNum WHERE id = :todoId")
    fun updateOrder(todoId: Int, orderNum: Int)

    @Transaction
    fun updateOrders(todoOrders: List<TodoOrder>) {
        todoOrders.forEach { order ->
            updateOrder(order.todoId, order.orderNum)
        }
    }
    @Query("update todo set is_expired=:isExpired where id=:todoId")
    fun updateIsExpired(todoId: Int, isExpired: Boolean)

    @Query("update todo set is_expired=1 where end_time>datetime('now') ")
    fun initTodoExpired()
}